package com.zfzs.post.modules.merchant.repository;

import com.zfzs.post.modules.merchant.domain.Merchant;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

/**
 * 商户
 *
 * @author ck
 * @date 2019-09-25
 */
public interface MerchantRepository extends JpaRepository<Merchant, Long>, JpaSpecificationExecutor {
    //查询自己名下商户个数
    @Query(value = "select count(m.agent_id) from merchant m where m.agent_id = ?1", nativeQuery = true)
    int merchantCount(Long id);

    //三个月没有交易 则沉默商户
    @Query(value = "SELECT " +
            "count(m.agent_id) " +
            "FROM " +
            "merchant m " +
            "WHERE " +
            "m.agent_id = ?1 and (" +
            "SELECT " +
            "IFNULL(SUM(t.amount), 0.00) " +
            "FROM " +
            "transaction_info t " +
            "WHERE " +
            "t.sn = m.sn " +
            "and date_format(t.transaction_time, '%Y%m') BETWEEN date_format( DATE_SUB(curdate(), INTERVAL 3 MONTH),  '%Y%m'  ) " +
            "and date_format( DATE_SUB(curdate(), INTERVAL 0 MONTH),  '%Y%m'  )"+
            ") = 0", nativeQuery = true)
    int silenceMerchantCount(Long id);



    //上个月交易超过50000 则优质商户
    @Query(value = "SELECT " +
            "count(m.agent_id) " +
            "FROM " +
            "merchant m " +
            "WHERE " +
            "m.agent_id = ?1 and (" +
            "SELECT " +
            "IFNULL(SUM(t.amount), 0.00) " +
            "FROM " +
            "transaction_info t " +
            "WHERE " +
            "t.sn = m.sn " +
            "AND date_format(t.transaction_time, '%Y%m') = date_format(" +
            "DATE_SUB(curdate(), INTERVAL 1 MONTH)," +
            "'%Y%m' " +
            ") " +
            ") >= 5000", nativeQuery = true)
    int highMerchantCount(Long id);

    //top10的商户排行交易
    @Query(value = "SELECT " +
            "m.merchant_name as merchantName," +
            "m.sn," +
            "m.phone," +
            "(SELECT t.is_activation FROM post_terminal_info t WHERE t.sn = m.sn) AS isActivation," +
            "( " +
            "SELECT " +
            "IFNULL(SUM(t.amount), 0.00) " +
            "FROM " +
            "transaction_info t " +
            "WHERE " +
            "t.sn = m.sn" +
            ") AS amount," +
            "(select p.url from picture p where p.id = (select i.head_img_id from agent_info i where i.id = m.agent_id))as headImgUrl " +
            "FROM " +
            "merchant m " +
            "WHERE " +
            "m.agent_id = ?1 and m.create_date >= ?2 and m.create_date <= ?3 " +
            "ORDER BY " +
            "amount DESC " +
            "LIMIT 0,10", nativeQuery = true)
    List<Map<String, Object>> queryTranTopTenList(Long id,String startDate,String endDate);

    //根据时间、代理商查询数量
    @Query(value = "SELECT count(id) FROM merchant WHERE agent_id in (?1) AND create_date >= ?2 AND create_date <= ?3", nativeQuery = true)
    int queryCountByAgentIds(List<Long> ids, String startDate, String endDate);

    //商户新增列表
    @Query(value = "SELECT " +
            "p.model as model," +
            "p.id as terminalTypeId," +
            "m.create_date as createDate " +
            "FROM " +
            "post_terminal_type p " +
            "INNER JOIN merchant m ON m.terminal_type_id = p.id " +
            "WHERE " +
            "p.channel_id = ?1 " +
            "AND m.agent_id IN (?2) " +
            "AND m.create_date >= ?3 " +
            "AND m.create_date <= ?4 " +
            "GROUP BY " +
            "m.create_date LIMIT ?5,?6", nativeQuery = true)
    List<Map<String, Object>> queryAddMerchantList(Long channelId, List<Long> ids, String startDate, String endDate,long pageNumber,int pageSize);

    //商户新增列表
    @Query(value = "SELECT " +
            "COUNT(m.agent_id) " +
            "FROM " +
            "merchant m " +
            "WHERE " +
            "m.agent_id IN (?1) " +
            "AND m.create_date >= ?2 " +
            "AND m.create_date <= ?3 " , nativeQuery = true)
    int queryTotalTerminalCount(List<Long> ids, String startDate, String endDate);

    //商户新增列表
    @Query(value = "SELECT " +
            "COUNT(p.model)"+
            "FROM " +
            "post_terminal_type p " +
            "INNER JOIN merchant m ON m.terminal_type_id = p.id " +
            "WHERE " +
            "p.channel_id = ?1 " +
            "AND m.agent_id IN (?2) " +
            "AND m.create_date >= ?3 " +
            "AND m.create_date <= ?4 " +
            "GROUP BY " +
            "m.create_date", nativeQuery = true)
    Integer queryAddMerchantListCount(Long channelId,List<Long> ids, String startDate, String endDate);

    //商户新增 每天的机器数量
    @Query(value = "SELECT COUNT(m.id) " +
            "FROM " +
            "merchant m " +
            "WHERE " +
            "m.agent_id IN (?1) " +
            "AND m.create_date >= ?2 " +
            "AND m.create_date <= ?3 " +
            "AND m.terminal_type_id = ?4", nativeQuery = true)
    int queryAddMerchantCount(List<Long> ids, String startDate, String endDate,Long terminalTypeId);

    //商户新增明细
    @Query(value = "SELECT " +
            "m.agent_id as agentId,( " +
            "SELECT " +
            "a.card_name " +
            "FROM " +
            "agent_verified a " +
            "WHERE " +
            "a.id = m.agent_id " +
            ")as cardName, " +
            "m.create_date as createDate, " +
            "m.sn as sn, " +
            "m.reg_status as regStatus, " +
            "( " +
            "SELECT " +
            "IF ( " +
            "p.is_activation = 0, " +
            "'未激活', " +
            "'已激活' " +
            ") " +
            "FROM " +
            "post_terminal_info p " +
            "WHERE " +
            "p.sn = m.sn " +
            ")as isActivation, " +
            "m.merchant_name as merchantName " +
            "FROM " +
            "merchant m " +
            "WHERE " +
            "m.agent_id IN (?1) " +
            "AND m.create_date >= ?2 " +
            "AND m.create_date <= ?3 and m.terminal_type_id = ?4 order by m.create_date desc ", nativeQuery = true)
    List<Map<String, Object>> queryAddMerchantInfoList(List<Long> ids, String startDate, String endDate,Long terminalTypeId);

    Merchant findBySn(String sn);
}